use sqlx::{PgPool, Error};
use struct_field_names_as_array::FieldNamesAsSlice;

use crate::core::pagination;
use crate::core::psqlx_tools::SqlString;
use crate::model::advert_position::AdvertPositionModel;
use crate::model::model::Model;
use crate::model::advert::AdvertModel;
use crate::types::backend::advert_type::{AdvertForm, AdvertListReq, AdvertRow, AdvertListResp};


pub struct AdvertService {}

impl AdvertService {
    /// 获取广告分页列表
    /// 参数args AdvertListReq，数据包括页码，每页数量，名称，分类id
    /// 正确返回 AdvertListResp 数据，数据包括页码，每页数量，当前页AdvertRow数据数组，总条数
    /// 错误返回 sqlx::Error
    pub async fn get_advert_page(db: &PgPool, args: &AdvertListReq) -> Result<AdvertListResp, Error> {
        let page = args.page.unwrap_or(1);
        let page_size = args.page_size.unwrap_or(10);
        let name = match &args.name {
            Some(data) => data,
            None => ""
        };
        let pid = args.pid.unwrap_or(0);

        let model = AdvertModel::new();
        let advert_position_model = AdvertPositionModel::new();

        // 需要连接查询的字段
        let row_fields = model.row_field().split(",");
        let mut join_fields = Vec::new();
        for field in row_fields {
            join_fields.push(format!("{}.{}", model.table(), field));
        }
        join_fields.push(format!("{}.{} AS advert_position_name", advert_position_model.table(), advert_position_model.name()));

  
        let mut sql = SqlString::new(model.table())
        .join_field(&join_fields)
        .join(model.pid(), advert_position_model.table(), advert_position_model.primary_key());
        
        let mut count_sql = SqlString::new(model.table())
        .join(model.pid(), advert_position_model.table(), advert_position_model.primary_key());
        
        // 搜索条件
        if !name.is_empty() {
            let like_name = format!("{}.{}", model.table(), model.name());
            sql = sql.where_like(&like_name);
            count_sql = count_sql.where_like(&like_name);
        }
        if pid > 0 {
            sql = sql.where_eq(model.pid());
            count_sql = count_sql.where_eq(model.pid());
        }
        // 分页数据
        let sql = sql.order_desc(format!("{}.{}", model.table(), model.primary_key()).as_str())
        .pagination(page, page_size);
        // 总数量
        let count_sql = count_sql.count();

        let mut query = sqlx::query_as::<_, AdvertRow>(&sql);
        let mut count_query = sqlx::query_scalar::<_, i64>(&count_sql);
        
        // 前端页码query参数
        let mut query_str = String::from("");
        // 搜索条件绑定
        if !name.is_empty() {
            query = query.bind(format!("%{}%", &name));
            count_query = count_query.bind(format!("%{}%", &name));
            query_str.push_str(format!("&{}={}", model.name(), name).as_str());
        }
        if pid > 0 {
            query = query.bind(pid);
            count_query = count_query.bind(pid);
            query_str.push_str(format!("&{}={}", model.pid(), pid).as_str());
        }

        let res = query.fetch_all(db).await;
        let list = match res {
            Ok(data) => data,
            Err(e) => {
                return Err(e)
            }
        };
        let total = count_query.fetch_one(db).await;
        let total_count = match total {
            Ok(data) => data,
            Err(e) => {
                return Err(e)
            }
        };


        let page_total = pagination::get_total_page(page_size, total_count);
        let page_str = pagination::pagination_link("/adverts", page, page_total, total_count, &query_str);

        
        let result = AdvertListResp{
            paginate: page_str,
            list
        };
        
        Ok(result)
    }

    /// 获取广告详情
    /// 参数 id 广告id
    /// 正确返回 AdvertForm 表单数据
    /// 错误返回 sqlx::Error
    pub async fn get_advert_by_id(db: &PgPool, id: i64) -> Result<AdvertForm, Error> {
        let model = AdvertModel::new();
        let sql = SqlString::new(model.table())
        .field_slice(AdvertForm::FIELD_NAMES_AS_SLICE)
        .append_primary_key()
        .where_primary_key()
        .select();

        let res = sqlx::query_as::<_, AdvertForm>(&sql)
        .bind(id)
        .fetch_one(db)
        .await;
        res
    }

    /// 添加广告
    /// 参数 data AdvertForm 表单数据
    /// 正确返回 bool true
    /// 错误返回 sqlx::Error
    pub async fn insert_advert(db: &PgPool, data: &AdvertForm) -> Result<bool, Error> {
        let model = AdvertModel::new();
        let sql = SqlString::new(model.table())
        .field_slice(AdvertForm::FIELD_NAMES_AS_SLICE)
        .insert();

        let res = sqlx::query(&sql)
        .bind(&data.pid)
        .bind(&data.name)
        .bind(&data.url)
        .bind(&data.image)
        .bind(&data.order_by)
        .execute(db)
        .await;

        match res {
            Ok(data) => Ok(data.rows_affected() > 0),
            Err(e) => Err(e)
        }
    }

    /// 更新广告
    /// 参数 data AdvertForm 表单数据
    /// 正确返回 bool true
    /// 错误返回 sqlx::Error
    pub async fn update_advert(db: &PgPool, id:i64, data: &AdvertForm) -> Result<bool, Error> {
        let model = AdvertModel::new();
        let sql = SqlString::new(model.table())
        .field_slice(AdvertForm::FIELD_NAMES_AS_SLICE)
        .where_primary_key()
        .update();

        let res = sqlx::query(&sql)
        .bind(id)
        .bind(&data.pid)
        .bind(&data.name)
        .bind(&data.url)
        .bind(&data.image)
        .bind(&data.order_by)
        .execute(db)
        .await;

        match res {
            Ok(data) => Ok(data.rows_affected() > 0),
            Err(e) => Err(e)
        }
    }

    /// 删除广告
    /// 参数 id 广告id
    /// 正确返回 bool true
    /// 错误返回 sqlx::Error
    pub async fn delete_advert(db: &PgPool, id: i64) -> Result<bool, Error> {
        let model = AdvertModel::new();
        let sql = SqlString::new(model.table())
        .where_primary_key()
        .delete();

        let res = sqlx::query(&sql)
        .bind(id)
        .execute(db)
        .await;

        match res {
            Ok(data) => Ok(data.rows_affected() > 0),
            Err(e) => Err(e)
        }
    }

    /// 广告是否存在
    /// 参数 id 区域id
    /// 正确返回 数量
    /// 错误返回 sqlx::Error
    pub async fn exist_advert_by_id(db: &PgPool, id: i64) -> Result<i64, Error> {
        let model = AdvertModel::new();
        let sql = SqlString::new(model.table())
        .where_primary_key()
        .count();

        let res = sqlx::query_scalar::<_, i64>(&sql)
        .bind(id)
        .fetch_one(db)
        .await;

        res
    }

    // 统计所有数量
    pub async fn all_count(db: &PgPool) -> i64 {
        let model = AdvertModel::new();
        let sql = SqlString::new(model.table()).count();
        let res = sqlx::query_scalar::<_, i64>(&sql).fetch_one(db).await;
        let total = match res {
            Ok(data) => data,
            Err(_) => 0
        };
        total 
    }
}
    